from odoo import fields, models


class StationUse(models.Model):
    _name = 's.station.use.report'
    _description = 'Station Use Report'
    _auto = False
    _order = 'use_date'

    picking_id = fields.Many2one('stock.picking')
    origin = fields.Char('Origin')
    location_id = fields.Many2one('stock.location', string='Origin Location')
    location_dest_id = fields.Many2one('stock.location', string='Dest Location')
    department_id = fields.Many2one('hr.department', string='Department')
    product_id = fields.Many2one('product.product', string='Product')
    product_code = fields.Char(related='product_id.default_code', string='Product Code')
    qty = fields.Float(string='Qty')
    uom_id = fields.Many2one('uom.uom', string='Uom')
    amount = fields.Float('Total amount')
    use_date = fields.Datetime('Use Date')
    user_id = fields.Many2one('res.users', 'Use User')
    station_id = fields.Many2one('work.station', 'Work Station')
    business_type = fields.Char('Business Type')
    currency = fields.Char('Currency')

    def action_report_download(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        station_id = self._context.get('station_id')
        business_data = ','.join([start_date or '', end_date or '', str(station_id) if station_id else ''])
        return {
            'type': 'ir.actions.act_url',
            'url': f'/download/station/use/report/excel/{business_data or ""}',
            'target': 'new',
        }

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        station_id = self._context.get('station_id')
        # location_ids = self.env['stock.location'].search(['|', ('barcode', '=', 'WH-PREPRODUCTION'), ('name', '=', 'Inventory loss')])
        loss_location_id = self.env['stock.location'].search([('name', '=', 'Inventory loss')], limit=1)
        production_location_id = self.env['stock.location'].search([('name', '=', 'Production')], limit=1)
        # id_list = location_ids.mapped('child_ids')
        # id_list |= location_ids
        # where = f"where pc.code not in ('BCP', 'CHP') and sl.id in {tuple(id_list.ids)}"
        one_where = f"where pc.code not in ('BCP', 'CHP', 'OPFP') and sml.state = 'done' and (sl2.id = {production_location_id.id} or sl.id = {production_location_id.id})"
        two_where = f"where pc.code not in ('BCP', 'CHP', 'OPFP') and sml.state = 'done' and (sl2.id = {loss_location_id.id} or sl.id = {loss_location_id.id})"
        if start_date:
            one_where += f"  and sml.date >= '{start_date}'  "
            two_where += f"  and sml.date >= '{start_date}'  "
        if end_date:
            one_where += f"  and sml.date <= '{end_date}'  "
            two_where += f"  and sml.date <= '{end_date}'  "
        if station_id:
            one_where += f"  and sp.work_station_id = {station_id}  "
            two_where += f"  and sp.work_station_id = {station_id}  "
        sql = f"""
        with cte as (
            select sp.id                                                        picking_id,
                   sm.origin                                                    origin,
                   sml.location_id                                              location_id,
                   sml.location_dest_id                                         location_dest_id,
                   hd.id                                                        department_id,
                   pp.id                                                        product_id,
                   case
                       when sl2.usage = 'internal' then sml.quantity * -1
                       else
                           sml.quantity end                                     qty,
                   uu.id                                                        uom_id,
                   (case
                        when sl2.usage = 'internal' then sml.quantity * -1
                        else
                            sml.quantity end) * abs(COALESCE(svl.unit_cost, 0)) amount,
                   sml.date                                                     use_date,
                   sp.apply_uid                                                 user_id,
                   ws.id                                                        station_id,
                   case
                       when sl2.usage = 'internal' then '生产消耗退回'
                       else
                           '生产消耗' end                                       business_type,
                    'THB' currency
            from stock_move_line sml
                     left join stock_move sm on sml.move_id = sm.id
                     left join stock_valuation_layer svl on sm.id = svl.stock_move_id
                     left join stock_picking sp on sm.picking_id = sp.id
                     left join stock_location sl on sml.location_id = sl.id
                     left join stock_location sl2 on sml.location_dest_id = sl2.id
                     left join work_station ws on sp.work_station_id = ws.id
                     left join hr_department hd on sp.apply_department_id = hd.id
                     left join product_product pp on sm.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
                     left join product_category pc on pt.categ_id = pc.id
                     left join uom_uom uu on pt.uom_id = uu.id
            {one_where}
            union all
            
            select sp.id                                                        picking_id,
                   sm.origin                                                    origin,
                   sml.location_id                                              location_id,
                   sml.location_dest_id                                         location_dest_id,
                   hd.id                                                        department_id,
                   pp.id                                                        product_id,
                   case
                       when sl2.usage = 'internal' then sml.quantity * -1
                       else
                           sml.quantity end                                     qty,
                   sml.product_uom_id                                           uom_id,
                   (case
                        when sl2.usage = 'internal' then sml.quantity * -1
                        else
                            sml.quantity end) * abs(COALESCE(svl.unit_cost, 0)) amount,
                   sml.date                                                     use_date,
                   sp.apply_uid                                                 user_id,
                   sp.work_station_id                                           station_id,
                   case
                       when sl2.usage = 'internal' then '领用退回'
                       else
                           '领用出库' end                                       business_type,
                   'THB' currency
            from stock_move_line sml
                     left join stock_location sl on sml.location_id = sl.id
                     left join stock_location sl2 on sml.location_dest_id = sl2.id
                     left join stock_move sm on sml.move_id = sm.id
                     left join stock_valuation_layer svl on sm.id = svl.stock_move_id
                     left join stock_picking sp on sm.picking_id = sp.id
                     left join product_product pp on sml.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
                     left join product_category pc on pc.id = pt.categ_id
                     left join hr_department hd on sp.apply_department_id = hd.id
            {two_where}
            )
            select row_number() OVER() AS id, * from cte
        """
        return sql
